'''
UNIT INFO
Module for pumping all data from one database to another

ADAP HIS
-functions:
    -pumpAllTables()
        -research handling null-types; options
            >-dynamic addition of fields only if not null
            -afterwards update 'null' to null (disgard this option)
-pump also parameters
-test the filled sqlite-db
    -create a test-list
-solve the found bugs

ADAP NO

ADAP FU
'''

import pyodbc
from pysqlite2 import dbapi2 as sqlite
import sys
import os
import jocallback

countit = 0

def cursorFromDbType(db_typest, database, sqlst, writebo):
    '''Create and execute a cursor from the sql-string'''
    if db_typest == 'ms access 2000':
        cnx = pyodbc.connect('dsn=' + database + ';UID=Admin;PWD=''')
    elif db_typest == 'sqlite3':
##        print database
        cnx = sqlite.connect(database)
        cnx.text_factory = str      # remove when upgrading to unicode

    cur = cnx.cursor()
    cur.execute(sqlst)
    if writebo: cnx.commit()      # necessary fot writes to database
    return cur

def getInfoFromDatabase(databasepathst='', versiononlybo = False):
    # sqlite only;
    infodi={}
    infost = ''
    try:
        infodi['db_name'] = os.path.basename(databasepathst)

        sqlst = 'SELECT DECIMALS FROM PARAMS WHERE ParamID = "database_implementation"'
        cursor = cursorFromDbType('sqlite3',databasepathst,sqlst, False)
        row =  cursor.fetchone()
        print type(row)

        if str(type(row))=="<type 'NoneType'>":
            infodi['db_version'] = 0
        else:
            infodi['db_version'] = row[0]
        print row

        if versiononlybo:
            infost = infodi['db_version']
        else:
            sqlst = 'SELECT * FROM RELATIONS'
            cursor = cursorFromDbType('sqlite3',databasepathst,sqlst, False)
            rows = cursor.fetchall()
            infodi['count_of_relations'] = len(rows)

        ##    print infodi
            for itemtu in infodi.items():
                infost = infost + itemtu[0] + " = " + str(itemtu[1]) + '\n'

        print infost
        cursor.close()
        return infost
    except:
        print str(sys.exc_info()[0]) +  '\n' + str(sys.exc_info()[1])
        return 'Could not acquire info (no valid db)'
    finally:
        pass


##    print 'dbversion: ' + str(db_versionst[0])

##def pumpTable(curSource, tablest, fieldchainst, targettypest, targetdb):
##
##    #  "INSERT INTO Relations (Object1,Relation) VALUES ('heelnieuw','relo')"
##    valueli = []
##    i = 0
##    for row in curSource:
##        for value in row:
##            valueli.append(value)
##
##        valuechainst = str(tuple( valueli))
##        sqltargetst = "INSERT INTO " + tablest + ' ' + fieldchainst + \
##                        " VALUES " + valuechainst
####        sqltargetst = "INSERT INTO History (Object,Time) VALUES ('gister','')"
####        sqltargetst= 'SELECT * FROM ' + tablest
##        print sqltargetst
##        print '\n'
##        curTarget = cursorFromDbType(targettypest, targetdb,
##                                        sqltargetst, True)
##        curTarget.close()
##        i = i + 1
##        if i == 2:
##            break

def deleteTableList(databasepathst, tableli):
    # sqlite only;
    for tablest in tableli:
        sqlst = "DELETE FROM " + tablest
##        print sqlst
        cursor = cursorFromDbType('sqlite3',databasepathst,sqlst, True)
        cursor.close()

def resetAutoIncrement(databasepathst, tableli):
    '''
    UNIT INFO:
    - some ID-fields are automatically incremented in the db
    - the highest number is remembered is a system-table 'sqlite_sequence'
    - reset this number to 1
    '''

    for tablest in tableli:
        sqlst = "UPDATE sqlite_sequence SET seq = 1 WHERE name = '" + tablest + "'"
##        print sqlst
        cursor = cursorFromDbType('sqlite3',databasepathst,sqlst, True)
        cursor.close()

def pumpAllTables(tableli, sourcetypest, sourcedb, targettypest, targetdb, 
                  programsettingstransferbo = False):

    '''
    UNIT INFO
    -Pump works now for the table Relations and Params
    (but usually only relations are imported)
    -max. number of recs set to: i = 100 000
    -this function implies that new fields are postfixed and not inserted
    '''

    jocallback.refreshGui()
    for tablest in tableli:
        if programsettingstransferbo:
            sqlsourcest = "SELECT * FROM " + tablest + " WHERE Scope = 'sys'"
        else:
            sqlsourcest = 'SELECT * FROM ' + tablest
        
        curSource = cursorFromDbType(sourcetypest, sourcedb, sqlsourcest, False)

        #  "INSERT INTO Relations (Object1,Relation) VALUES ('heelnieuw','relo')"

        if tablest == 'Relations':

            allfieldli = ['RelID','Subject','Object1','Relation','Object2','Selected',
                                'Change','Remark','ModDate','Source','MsNumber',
                                'MsDate','MsCurrency','MsLongText','CreaDate',
                                'Creator','Project','ImportDate','ImportedFrom',
                                'Num_parents','Num_children','Depth_left',
                                'Depth_right','ModSource']

            i = 0
            for row in curSource:
                print 'i = ' + str(i)
                valueli = []
                j = 0
                usedfieldli = []
                for value in row:
##                    print value
##                    print 'j = ' + str(j)

                    if value != None and value != '' and j > 0: # skip first autoincremental field
                        # add field to used-fieldchain
                        usedfieldli.append(allfieldli[j])
                        # add value to valuechain
                        if j == 5:
                            valueli.append(int(row[j])) # select
                        elif j in [10,12]:
                            valueli.append(float(row[j]))
                        else:
##                            valueli.append(str(row[j]))
                            valueli.append(row[j])
                    j = j + 1

                fieldchainst = '(' + ','.join(usedfieldli) + ')'

##                valuechainst = '(' + ','.join(valueli) + ')'  # impossible with non-string fields
##                valuechainst = str(tuple( valueli))   # gives escape-duplications

                valuechainst = str(tuple( valueli))     # gives escape-duplications
                # string-conversion of lists has duplicated escape-chars, so remove them
                valuechainst = valuechainst.replace('\\\\\\\\\\\\\\\\','\\')
                valuechainst = valuechainst.replace('\\\\\\\\','\\')
                valuechainst = valuechainst.replace('\\\\','\\')
                valuechainst = valuechainst.replace("\\n", "\n")

##                print valuechainst

                sqltargetst = "INSERT INTO " + tablest + ' ' + fieldchainst + \
                                " VALUES " + valuechainst
##                print sqltargetst
##                print '\n'
                curTarget = cursorFromDbType(targettypest, targetdb,
                                                sqltargetst, True)
                curTarget.close()
                i = i + 1
                if i == 100000:
                    break


        if tablest == 'PARAMS':

            '''
            Params-table requires to overwrite matching records:
            - walk thru source-table
            - find matching target-record
            - overwrite matching target-record with source-data (only data-fields)
            - this leaves new target-records intact (newly added options)
            - programsettingstransferbo is used to write the program-settings from
            the settings.db to the userdata-db (knobjex_sqlite.db by default)
            '''

            allfieldli = ['ParamID','Scope','Cluster','ClusterOrder','Name',
                            'Description','IsEditableByUser','IsHiddenFromUser',
                            'Datatype','BOOLEANS','BOO_LST','BOO_DEF','STRINGS',
                            'STR_LST','STR_DEF','INTEGERS','INT_LST','INT_DEF',
                            'DECIMALS','DEC_LST','DEC_DEF','DATES',
                            'DAT_LST','DAT_DEF']

            updatefieldli = ['BOOLEANS','STRINGS','INTEGERS','DECIMALS','DATES']

            i = 0
            for row in curSource:
                print 'i = ' + str(i)
                j = 0
                setcritli = []
                skipbo = False
                for value in row:
                    print value
                    print 'j = ' + str(j)
                    if j==0:
                        paramidst = value
                    elif j==6:  # if IsEditablebyUser is false you dont want any change unless for programsettingstransfer
                        if value == 0 and not programsettingstransferbo:
                            skipbo = True
                    elif value != None and value != '' and allfieldli[j] in updatefieldli and not skipbo: 

                        if allfieldli[j] in ['BOOLEANS', 'INTEGERS']:
                            setcritli.append(allfieldli[j] + '=' + str(int(value)))
                        elif allfieldli[j] in ['DECIMALS', 'DATES']:
                            setcritli.append(allfieldli[j] + '=' + str(float(value)))
                        else:
                            setcritli.append(allfieldli[j] + '="' + str(value) + '"')

                    j = j + 1

                setcritchainst = ','.join(setcritli)
##                setcritchainst = str(tuple( setcritli))   # gives escape-duplications
##                setcritchainst = setcritchainst[1:-1]
                

                print setcritchainst
                
                # Update: UPDATE tab1 SET col1 = a, col2 = b WHERE  col0= x
                
                sqltargetst = 'UPDATE ' + tablest + ' SET ' + setcritchainst + \
                                ' WHERE ParamID = "' + paramidst + '"'
                print sqltargetst
                print '\n'
                if len(setcritchainst) > 0:  # no change can be made
                    curTarget = cursorFromDbType(targettypest, targetdb,
                                                    sqltargetst, True)
                    curTarget.close()

                i = i + 1
                if i == 1000:
                    break

        curSource.close()


if __name__ == '__main__':

#-----------------------

    pass
##    targetpathst = 'M:/EigThuis/Bestanden/Leuk/Hobbies/Programmeren/Python/MijnCode/PyKnow/database/testdeze.db'
##    targetpathst = 'M:\\EigThuis\\Bestanden\\Leuk\\Hobbies\\Programmeren\\Python\\MijnCode\\PyKnow\\database\\testdeze.db'
##    targetpathst =  'M:\\EigThuis\\Bestanden\\Planning en Beheer\\Processen en kennis\\kb_sqlite_eigen\\eigen_knobjex.db'
##    pumpAllTables(['Relations'], 'ms access 2000', 'KnobjexProductie',
##                                'sqlite3', targetpathst)

#---------------------------
##    sourcepathst = 'M:\\EigThuis\\Bestanden\\Leuk\\Hobbies\\Programmeren\\Python\\MijnCode\\PyKnow\\database\\knobjex_sqlite.db'
##    targetpathst =  'M:\\EigThuis\\Bestanden\\Leuk\\Hobbies\\Programmeren\\Python\\MijnCode\\PyKnow\\database\\knobjex_sqlite_new.db'
##    pumpAllTables(['Relations'], 'sqlite3', sourcepathst,
##                                'sqlite3', targetpathst)
#------------------
##    sourcepathst = '/home/joris/test/db-test/knobjex_sqlite_A.db'
##    targetpathst =  '/home/joris/test/db-test/knobjex_sqlite_B.db'
##    pumpAllTables(['PARAMS'], 'sqlite3', sourcepathst,
##                                'sqlite3', targetpathst)

#-------------
##    dbpathst =  'M:\\EigThuis\\Bestanden\\Planning en Beheer\\Processen en kennis\\kb_sqlite_eigen\\eigen_knobjex.db'
##    getInfoFromDatabase(dbpathst, True)


